﻿using System;
using SmartSiteManagementSystem.IRepository.Material;
using SmartSiteManagementSystem.Models.Material;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;

namespace SmartSiteManagementSystem.Repository.Material
{
    public class MaterialInfoRepository : IMaterialInfoRepository
    //,IDisposable
    {
        #region dataTable
        /*
create table `material`( 
	`material_id` int(11) not null AUTO_INCREMENT primary key, 
	`material_name` varchar(50) not null ,
	`catalog_id` int not null,
	`single_weight` decimal(5,2) not null,
	`sort_order` int not null,
	`is_deleted`  bit not null default 0,
	`is_stop`  bit not null default 0,
	`descript` text null,
    `total_num`  int not null default 0,
    `last_updatetime` datetime not null default now(),
	`create_time` datetime not null default now(),
	`update_time` datetime not null default now()
    ) default charset=utf8;
         */
        #endregion
       // private IDbConnection _readConn;
        //private IDbConnection _writeConn;

        public bool Delete(Hashtable param)
        {
            bool back = false;
            if (param == null)
                param = new Hashtable();
            String querySql = @"delete from material";
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where ");
                    int parCount = 0;
                    if (param.ContainsKey("material_id"))
                    {
                        pars.Add("material_id", param["material_id"]);

                        whereCondition.Append(" material_id =@material_id");
                        parCount++;
                    }
                    if (param.ContainsKey("material_name"))
                    {
                        pars.Add("material_name", param["material_name"]);
                        if(parCount>0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("material_name =@material_name");
                        parCount++;
                    }
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        if (parCount > 0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("catalog_id =@catalog_id");
                        parCount++;
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        if (parCount > 0)
                        {
                            whereCondition.Append(" and ");
                        }
                        whereCondition.Append("is_deleted =@is_deleted");
                        parCount++;
                    }
                }
                string execSql = querySql + whereCondition.ToString();
                int backInt = _writeConn.Execute(execSql, pars);
                if(backInt>0)
                {
                    back = true;
                }
            }
            return back;
        }

        public MaterialInfo Get(Hashtable param)
        {
            MaterialInfo back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select material_id as MaterialId,material_name as MaterialName,catalog_id as CatalogId,single_weight as SingleWeight,sort_order as SortOrder,is_deleted as IsDeleted,is_stop as IsStop,descript as Descript,total_num as TotalNum,last_updatetime as LastUpdateTime, create_time as CreateTime,update_time as UpdateTime from  material";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {

                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("material_id"))
                    {
                        pars.Add("material_id", param["material_id"]);
                        whereCondition.Append(" and material_id =@material_id");
                    }
                    if (param.ContainsKey("material_name"))
                    {
                        pars.Add("material_name", param["material_name"]);
                        whereCondition.Append(" and material_name =@material_name");
                    }
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        whereCondition.Append(" and catalog_id =@catalog_id");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                }
                whereCondition.Append(" limit 1");
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.QueryFirstOrDefault<MaterialInfo>(execSql, pars);
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public int GetCount(Hashtable param)
        {
            int back = 0;
            if (param == null)
                param = new Hashtable();
            String querySql = "select count(material_id) from material";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("material_id"))
                    {
                        pars.Add("material_id", param["material_id"]);
                        whereCondition.Append(" and material_id =@material_id");
                    }
                    if (param.ContainsKey("material_name"))
                    {
                        pars.Add("material_name", param["material_name"]);
                        whereCondition.Append(" and material_name =@material_name");
                    }
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        whereCondition.Append(" and catalog_id =@catalog_id");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                }
                
                string execSql = querySql + whereCondition.ToString();
                #endregion
                back = _readConn.QueryFirstOrDefault<int>(execSql, pars);
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <param name="orderBy"></param>
        /// <param name="first"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public IList<MaterialInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {
            IList<MaterialInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select material_id as MaterialId,material_name as MaterialName,catalog_id as CatalogId,single_weight as SingleWeight,sort_order as SortOrder,is_deleted as IsDeleted,is_stop as IsStop,descript as Descript,total_num as TotalNum,last_updatetime as LastUpdateTime, create_time as CreateTime,update_time as UpdateTime from  material";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("material_id"))
                    {
                        pars.Add("material_id", param["material_id"]);
                        whereCondition.Append(" and material_id =@material_id");
                    }
                    if (param.ContainsKey("material_name"))
                    {
                        pars.Add("material_name", param["material_name"]);
                        whereCondition.Append(" and material_name =@material_name");
                    }
                    if (param.ContainsKey("catalog_id"))
                    {
                        pars.Add("catalog_id", param["catalog_id"]);
                        whereCondition.Append(" and catalog_id =@catalog_id");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by material_id desc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<MaterialInfo>(execSql, pars).AsList<MaterialInfo>();
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(MaterialInfo obj)
        {
            int back = 0;
            #region sql
            string sql = @"insert into material(
material_name,
catalog_id,
single_weight,
sort_order,
is_deleted,
is_stop,
descript,
total_num,
last_updatetime,
create_time,
update_time) VALUES(
@material_name,
@catalog_id,
@single_weight,
@sort_order,
@is_deleted,
@is_stop,
@descript,
@total_num,
@last_updatetime,
@create_time,
@update_time
      );select last_insert_id();";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                back = _writeConn.QueryFirst<int>(sql, new
                {
                    material_name=obj.MaterialName,
                    catalog_id= obj.CatalogId,
                    single_weight= obj.SingleWeight,
                    sort_order= obj.SortOrder,
                    is_deleted= obj.IsDeleted,
                    is_stop= obj.IsStop,
                    descript= obj.Descript,
                    total_num= obj.TotalNum,
                    last_updatetime= obj.LastUpdateTime,
                    create_time= obj.CreateTime,
                    update_time= obj.UpdateTime
                });
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool Update(MaterialInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @"update material
set
material_name = @material_name,
catalog_id = @catalog_id,
single_weight = @single_weight,
sort_order = @sort_order,
is_deleted = @is_deleted,
is_stop = @is_stop,
descript = @descript,
total_num = @total_num,
last_updatetime= @last_updatetime,
create_time = @create_time,
update_time = @update_time
WHERE material_id =@material_id;";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

               int backint = _writeConn.Execute(sql, new
                {
                   material_id = obj.MaterialId,
                   material_name = obj.MaterialName,
                   catalog_id = obj.CatalogId,
                   single_weight = obj.SingleWeight,
                   sort_order = obj.SortOrder,
                   is_deleted = obj.IsDeleted,
                   is_stop = obj.IsStop,
                   descript = obj.Descript,
                   total_num = obj.TotalNum,
                   last_updatetime = obj.LastUpdateTime,
                   create_time = obj.CreateTime,
                   update_time = obj.UpdateTime
               });
                if(backint>0)
                {
                    back = true;
                }
            }
            return back;
        }
    }
}

